package com.example.blog.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.example.blog.domain.Article;
import com.example.blog.utils.JDBCUtils;


public class ArticleDAO {
	public int insert(Article a) throws SQLException {
		// 1. 获取连接
		Connection conn = JDBCUtils.getConnection();

		// 2. 创建Statment
		String sql = "insert into article(title,content) values (?,?)";
		PreparedStatement stmt = conn.prepareStatement(sql);

		stmt.setString(1, a.getTitle());
		stmt.setString(2, a.getContent());

		// 3. 执行层语句
		int result = stmt.executeUpdate();

		// 4. 释放资源
		JDBCUtils.destory(conn, stmt, null);

		return result;
	}

	public int deleteById(int id) throws SQLException {

		return 0;
	}

	public int update(Article a) throws SQLException {

		return 0;
	}

	public Article findById(int id) throws SQLException {

		return null;
	}

	public List<Article> findAll() throws SQLException {

		// 1. 获取连接
		Connection conn = JDBCUtils.getConnection();

		// 2. 创建Statment
		String sql = "select * from article";
		PreparedStatement stmt = conn.prepareStatement(sql);

		// 3. 执行层语句
		ResultSet rs = stmt.executeQuery();
		List<Article> list =new ArrayList<>();
		while(rs.next())
		{
			int id = rs.getInt(1);
			String title = rs.getString(2);
			String content = rs.getString(3);
			
			Date gmtCreate = rs.getTime(5);
			Date gtmModified = rs.getTime(6);
			
			Article a = new Article();
			a.setId(id);;
			a.setTitle(title);
			a.setContent(content);
			a.setGmtCreate(gmtCreate);
			a.setGmtModifed(gtmModified);
			
			list.add(a);
			
		}

		// 4. 释放资源
		JDBCUtils.destory(conn, stmt, null);

		return list;
	}

	
	
	/**
	 * 查询所有数量
	 * @return	返回国家总数
	 * @throws SQLException
	 */
	public int getCount() throws SQLException
	{
		Connection conn = JDBCUtils.getConnection();
		String sql = "select count(*) from article";
		PreparedStatement stmt = conn.prepareStatement(sql);
		ResultSet rs = stmt.executeQuery();
		int count=0;
		if(rs.next()) 
		{
			count = rs.getInt(1);
		}
		JDBCUtils.destory(conn, stmt, rs);
		return count;
	}
	
	/**
	 * 查询分页数据
	 * @param start	查询开始位置
	 * @param count	查询数量
	 * @return
	 * @throws SQLException
	 */
	public List<Article> findPage(int start,int count) throws SQLException
	{
		// 1. 连接
		Connection conn = JDBCUtils.getConnection();
		// 2.
		String sql = "select * from article limit ?,?";
		PreparedStatement stmt = conn.prepareStatement(sql);
		stmt.setInt(1,start);
		stmt.setInt(2,count);
		// 3.
		ResultSet rs = stmt.executeQuery();
		List<Article> list =new ArrayList<>();
		while(rs.next())
		{
			int id = rs.getInt(1);
			String title = rs.getString(2);
			String content = rs.getString(3);
			
			Date gmtCreate = rs.getTime(5);
			Date gtmModified = rs.getTime(6);
			
			Article a = new Article();
			a.setId(id);;
			a.setTitle(title);
			a.setContent(content);
			a.setGmtCreate(gmtCreate);
			a.setGmtModifed(gtmModified);
			
			list.add(a);
			
		}
		JDBCUtils.destory(conn, stmt, rs);
		return list;
	}
}